# _*_ coding:utf-8 _*_
# @Time  : 2022-02-17
# @Author: zizle
import json
import datetime
from collections import defaultdict
import numpy as np
import pandas as pd
from fastapi import APIRouter, Header, Query, Body
from pydantic import BaseModel
from v1_all_api.all_response import AllResponse
from v1_all_api.all_utils import datetime_utils, datalib_utils
from v1_all_api import constant
from hutool import security
from db import FAConnection
from configs import FILE_STORAGE, STATIC_FILE_HOST

variety_db_api = APIRouter()


@variety_db_api.get('/sheet/')  # 查询品种下的表
def get_variety_sheet(ruizy_token: str = Header(...), variety: str = Query(...), own: bool = Query(False),
                      group: int = Query(0)):
    user_obj = security.decrypt_access_token(ruizy_token)
    if not user_obj:
        return AllResponse.no_authorization()
    # 查询品种数据表
    user_id = user_obj['uid'] if own else 'NONE'
    sql = """
        SELECT id,name,variety_en,sorted,is_open,group_id, 
            (SELECT COUNT(id) FROM datalib_variety_chart WHERE st.id=sheet_id AND IF(%s='NONE',TRUE,user_id=%s)
            ) AS chart_count 
        FROM datalib_variety_sheet As st
        WHERE st.group_id=%s AND st.variety_en=%s AND IF(%s='NONE',TRUE,st.user_id=%s);
    """
    prm = [user_id, user_id, group, variety, user_id, user_id]
    db_conn = FAConnection()
    records = db_conn.query(sql, prm)
    records = list(filter(lambda x: datalib_utils.private_handler(x, user_obj), records))
    return AllResponse.operate_successfully(data=records)


@variety_db_api.post('/sheetData/')  # 查询一个表的详细数据
async def get_sheet_data(sid: int = Body(..., ge=1, embed=True), filename: str = Body(None, embed=True)):
    # 查询该表下的数据列
    column_sql = "SELECT id,name,sheet_id,formula,frequency,unit FROM datalib_sheet_column WHERE sheet_id=%s;"
    db_conn = FAConnection()
    indexes = db_conn.query(column_sql, param=[sid])
    # 通过这些指标与公式设置，获取具体数据
    df, table_headers = await datalib_utils.get_index_formula_dataframe(column_templates=indexes)
    if filename:  # 导出excel文件
        now_ts = int(datetime.datetime.now().timestamp() * 1000)
        excel_filename = f'{FILE_STORAGE}Temp/DataSheet/{filename}{now_ts}.xls'
        header = ['日期'] + [h['name'] for h in table_headers]
        # first_row = ['单位'] + [r['unit'] for r in table_headers]
        df.to_excel(excel_filename, index=False, header=header)
        resp_data = {'file_url': STATIC_FILE_HOST + f'Temp/DataSheet/{filename}{now_ts}.xls'}
    else:
        resp_data = {'table_value': df.to_dict(orient='records'), 'table_header': table_headers}
    return AllResponse.operate_successfully(data=resp_data)


@variety_db_api.get('/sheetChart/')  # 查询一个表下的数据图形
def get_sheet_chart(ruizy_token: str = Header(...), sid: int = Query(..., ge=1)):
    user_obj = security.decrypt_access_token(ruizy_token)
    if not user_obj:
        return AllResponse.no_authorization()
    sql = """
        SELECT id,chart_type,name,user_id,is_open,watermark,sorted,startdate,enddate,xaxis_format 
        FROM datalib_variety_chart WHERE sheet_id=%s ORDER BY sorted,id;
    """
    prm = [sid]
    db_conn = FAConnection()
    records = db_conn.query(sql, prm)
    # 不是管理员，只返回is_open=1的数据(是自己都返回)
    records = list(filter(lambda x: datalib_utils.private_handler(x, user_obj), records))
    response_data = []
    for item in records:
        response_data.append({
            'id': item['id'],
            'chart_type': item['chart_type'],
            'chart_type_zh': constant.CHART_TYPES.get(item['chart_type']),
            'name': item['name'],
            'watermark': item['watermark'],
            'sorted': item['sorted'],
            'startdate': item['startdate'],
            'enddate': item['enddate'],
            'xaxis_format': item['xaxis_format'],
            'option': {
                'title': {'text': item['name']}
            }
        })
    return AllResponse.operate_successfully(data=response_data)


@variety_db_api.get('/chart/')  # 查询品种下的图形名称列表
def get_variety_chart(ruizy_token: str = Header(...), variety: str = Query(...), own: bool = Query(False),
                      group: int = Query(0)):
    user_obj = security.decrypt_access_token(ruizy_token)
    if not user_obj:
        return AllResponse.no_authorization()
    user_id = user_obj['uid'] if own else 'NONE'
    # 查询品种数据图
    sql = """
        SELECT id,chart_type,name,user_id,is_open,watermark,sorted,startdate,enddate,xaxis_format,group_id  
        FROM datalib_variety_chart WHERE group_id=%s AND variety=%s 
        AND IF(%s='NONE',TRUE,user_id=%s) ORDER BY sorted,id;
    """
    prm = [group, variety, user_id, user_id]
    db_conn = FAConnection()
    records = db_conn.query(sql, prm)
    # 不是管理员，只返回is_open=1的数据(是自己都返回)
    records = list(filter(lambda x: datalib_utils.private_handler(x, user_obj), records))
    response_data = []
    for item in records:
        response_data.append({
            'id': item['id'],
            'chart_type': item['chart_type'],
            'chart_type_zh': constant.CHART_TYPES.get(item['chart_type']),
            'name': item['name'],
            'watermark': item['watermark'],
            'sorted': item['sorted'],
            'startdate': item['startdate'],
            'enddate': item['enddate'],
            'xaxis_format': item['xaxis_format'],
            'option': {
                'title': {'text': item['name']}
            },
            'link_ratios': [],
            'group_id': item['group_id'],
        })
    return AllResponse.operate_successfully(data=response_data)


@variety_db_api.get('/chartOption/')  # 根据ID获取图形具体配置数据
async def get_chart_option(cid: int = Query(..., ge=1), new_color: bool = True):
    query_sql = 'SELECT id,chart_type,name,startdate,enddate,xaxis_format,options,indication ' \
                'FROM datalib_variety_chart WHERE id=%s;'
    db_conn = FAConnection()
    chart_obj = db_conn.query(query_sql, [cid], fetchone=True)[0]
    if not chart_obj:
        return AllResponse.server_no_content()
    # 查询数据，并处理出图形echarts的配置里需要的data
    indication = json.loads(chart_obj['indication'])  # 需要的指标
    chart_option = json.loads(chart_obj['options'])  # 图形配置空壳

    df, headers = await datalib_utils.get_index_formula_dataframe(column_templates=indication, na_fill=np.NAN,
                                                                  start_date=chart_obj['startdate'],
                                                                  end_date=chart_obj['enddate'])
    df['datadate'] = df['datadate'].apply(lambda x: x.strftime(chart_obj['xaxis_format']))

    # 环比数据计算
    link_ratios = []
    lr_df = df.tail(5)
    for col in headers:
        if not col['isChecked']:
            continue
        lr_df['link_ratio'] = lr_df[col['prop']].pct_change().round(4)
        lr_df['_link_ratio_symbol_'] = lr_df[col['prop']].shift(1).apply(lambda x: -1 if x < 0 else 1)
        lr_df['link_ratio'] = lr_df['link_ratio'] * lr_df['_link_ratio_symbol_']
        del lr_df['_link_ratio_symbol_']
        lr_df = lr_df.replace([np.inf, -np.inf], np.nan)  # 替代无穷大为nan
        # lr_df = lr_df.fillna('').copy()  # 替代nan为''  # 由于抽取公共tail(5)，下方组织数据时判断,不然影响其他计算报错
        link_ratios.append({
            'name': col['name'],
            'unit': col['unit'],
            'datadate': lr_df.iloc[-1]['datadate'],
            'datavalue': '' if pd.isna(lr_df.iloc[-1][col['prop']]) else lr_df.iloc[-1][col['prop']],
            'link_ratio': '' if pd.isna(lr_df.iloc[-1]['link_ratio']) else lr_df.iloc[-1]['link_ratio'],
        })
        # if cid in [94, 97]:
        #     print(col)
        #     print(lr_df)
        #     print(link_ratios)

    df.fillna('', inplace=True)  # 填充NAN数据
    # ------- 源数据处理完毕
    # 填充option中的data数据
    # 1. xAxisData 2. series 3. legendData
    if chart_obj['chart_type'] == 'season':  # 季节图形
        # 取出作图列的prop
        prop_name = ''
        for i_item in indication:
            if i_item['isChecked']:
                prop_name = i_item['prop']
                break
        x_axis_data = datetime_utils.date_of_year()
        # 数据源按年分组
        df['year'] = df['datadate'].apply(lambda x: x[:4])
        df['date'] = df['datadate'].apply(lambda x: x[5:])
        series = []
        legend_data = []
        for year_group in df.groupby(by='year').groups:
            y_df = pd.merge(pd.DataFrame(data=x_axis_data, columns=['date']), df[df['year'] == year_group], on='date',
                            how='left')
            # y_df.fillna(None, inplace=True)
            series.append({
                'symbol': 'none', 'connectNulls': True, 'name': year_group,
                'type': 'line', 'yAxisIndex': 0,
                'data': list(
                    map(lambda x: None if pd.isna(x[prop_name]) else x[prop_name], y_df.to_dict(orient='records')))
            })
            legend_data.append({'name': year_group})
    else:  # 普通图形
        chart_source_data = df.to_dict(orient='records')
        x_axis_data = list(map(lambda x: x['datadate'], chart_source_data))
        series = []
        legend_data = []
        for i_item in indication:
            if i_item['isChecked']:
                o_series = {'symbol': 'none', 'connectNulls': True, 'name': i_item['name'],
                            'type': i_item['chartType'], 'yAxisIndex': i_item['yIndex'],
                            'data': list(map(lambda x: x[i_item['prop']], chart_source_data))}
                series.append(o_series)
                legend_data.append({'name': i_item['name']})
    # 放入具体的配置中
    chart_option['xAxis'][0]['data'] = x_axis_data
    chart_option['series'] = series
    chart_option['legend']['data'] = legend_data
    # 十字光标 原来有tooltip={trigger:axis}
    chart_option['tooltip']['axisPointer'] = {'type': 'cross'}
    # 颜色
    chart_option['color'] = ['#5b9bd5', '#ed7d31', '#ffc000', '#4472c4', '#70ad47', '#255e91',
                             '#9e480e', '#636363', '#997300', '#264478', '#43682b', '#7cafdd',
                             '#f1975a', '#ffcd33', '#698ed0', '#8cc168', '#327dc2', '#d26012']
    # 新版颜色
    if new_color:
        chart_option['color'] = ['#5b9bd5', '#ed7d31', '#a5a5a5', '#ffc000', '#4472c4',
                                 '#70ad47', '#255e91', '#c5dbe1', '#636363', '#997300']
        chart_option['title'].setdefault('textStyle', {})['color'] = '#595959'
        chart_option['title'].setdefault('textStyle', {})['fontFamily'] = 'Microsoft YaHei'
        chart_option['legend'].setdefault('textStyle', {})['color'] = '#595959'

    # 标题居中
    chart_option['title']['left'] = 'center'
    # 放入当前请求图形的id
    chart_option['chart_id'] = cid
    response_data = {
        'chart_option': chart_option,
        'link_ratios': link_ratios,
        'compared': 'day'  # 环比的频率
    }
    return AllResponse.operate_successfully(data=response_data)


class ChartGroup(BaseModel):
    chart_id: int
    group_id: int


@variety_db_api.post('/chartGroup/')  # 修改图形的类别
def modify_chart_group(ruizy_token: str = Header(...), modify_item: ChartGroup = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    db_conn = FAConnection()
    chart_sql = "SELECT id,user_id FROM datalib_variety_chart WHERE id=%s LIMIT 1;"
    chart_info = db_conn.query(chart_sql, param=[modify_item.chart_id], fetchone=True)[0]
    if not chart_info:
        return AllResponse.server_no_content()
    if chart_info['user_id'] != person['uid']:
        return AllResponse.validate_error(msg='请不要修改他人数据!')
    db_conn = FAConnection()
    sql = 'UPDATE datalib_variety_chart SET group_id=%s WHERE id=%s LIMIT 1;'
    _, success = db_conn.execute(sql, param=[modify_item.group_id, modify_item.chart_id])
    return AllResponse.operate_successfully() if success else AllResponse.server_error()


class SheetGroup(BaseModel):
    sheet_id: int
    group_id: int


@variety_db_api.post('/sheetGroup/')  # 修改图形的类别
def modify_chart_group(ruizy_token: str = Header(...), modify_item: SheetGroup = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    db_conn = FAConnection()
    chart_sql = "SELECT id,user_id FROM datalib_variety_sheet WHERE id=%s LIMIT 1;"
    chart_info = db_conn.query(chart_sql, param=[modify_item.sheet_id], fetchone=True)[0]
    if not chart_info:
        return AllResponse.server_no_content()
    if chart_info['user_id'] != person['uid']:
        return AllResponse.validate_error(msg='请不要修改他人数据!')
    db_conn = FAConnection()
    sql = 'UPDATE datalib_variety_sheet SET group_id=%s WHERE id=%s LIMIT 1;'
    _, success = db_conn.execute(sql, param=[modify_item.group_id, modify_item.sheet_id])
    return AllResponse.operate_successfully() if success else AllResponse.server_error()
